package persistence.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.rmi.ServerException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/** Database Class that connect to the database */
public class SQLDatabase
{
  private static final File dbFile        = new File("hit.sqlite");
  private static String     connectionURL = "jdbc:sqlite:" + dbFile.getPath();

  private boolean           initialized   = false;

  /**
   * load the database driver
   * 
   * @throws ServerException
   * */
  private static void initialize()
  {
    try
    {
      final String driver = "org.sqlite.JDBC";
      Class.forName(driver);
    }
    catch (ClassNotFoundException e)
    {
      System.err.println("Can't load database");
      e.printStackTrace();
      return;
    }

    if (!dbFile.exists())
    {
      // Create an empty sqlite database
      try
      {
        new java.io.FileOutputStream(dbFile).close();
        importSQL();
      }
      catch (IOException | SQLException e)
      {
        System.err.println("Error creating database file " + dbFile.getAbsolutePath() + ": "
            + e.getMessage());
        e.printStackTrace();
        return;
      }
    }
  }

  private static void importSQL() throws SQLException
  {
    Scanner s;
    File statementsFile = new File("create_tables.sql");
    FileInputStream is;
    try
    {
      is = new FileInputStream(statementsFile);
    }
    catch (FileNotFoundException e)
    {
      System.err.println("Could not initialize database; file not found: "
          + statementsFile.getAbsolutePath());
      return;
    }

    s = new Scanner(is);
    s.useDelimiter("(;(\r)?\n)|(--\n)");
    Statement st = null;
    try
    {
      Connection conn = new SQLDatabase().getConnection();
      st = conn.createStatement();
      while (s.hasNext())
      {
        String line = s.next().trim();

        if (line.startsWith("--") || line.length() == 0)
          continue;

        st.execute(line);
      }
    }
    finally
    {
      s.close();
      if (st != null)
        st.close();
    }
  }

  private SQLItemsDAO             items;
  private SQLProductContainersDAO productContainers;
  private SQLProductsDAO          products;
  private Connection              connection;

  /** Constructor */
  public SQLDatabase()
  {
    if (!initialized)
      initialize();

    items = new SQLItemsDAO(this);
    productContainers = new SQLProductContainersDAO(this);
    products = new SQLProductsDAO(this);
  }

  // get methods
  public SQLItemsDAO getItems()
  {
    return items;
  }

  public SQLProductContainersDAO getProductContainers()
  {
    return productContainers;
  }

  public SQLProductsDAO getProducts()
  {
    return products;
  }

  public Connection getConnection()
  {
    if (connection == null)
      try
      {
        connection = DriverManager.getConnection(connectionURL);
      }
      catch (SQLException e)
      {
        System.out.println("Error opening database: " + e.getMessage());
        e.printStackTrace();
      }

    return connection;
  }

  /** Open a connection to the database and start a transaction */
  public void startTransaction()
  {
    try
    {
      // Open a database connection
      connection = DriverManager.getConnection(connectionURL);

      // Start a transaction
      connection.setAutoCommit(false);
    }
    catch (SQLException e)
    {
      System.out.println("Can't open a connection to the database and start a transaction");
      e.printStackTrace();
    }
  }

  /**
   * Commit or rollback the transaction and close the connection
   * 
   * @throws SQLException
   * @throws ServerException
   * */
  public void endTransaction(boolean commit)
  {
    try
    {
      if (commit)
      {
        connection.commit();
      }
      else
      {
        connection.rollback();
      }
    }
    catch (SQLException e)
    {
      System.out.println("Can't commit or rollback the transaction and close the connection");
      e.printStackTrace();
    }
    finally
    {
      try
      {
        connection.close();
      }
      catch (SQLException e)
      {
        System.out.println("Can't close the connection");
        e.printStackTrace();
      }
    }
    connection = null;
  }
}
